package com.quyl.poi.util;

import cn.hutool.core.util.StrUtil;
import com.quyl.poi.dto.SheetCondition;
import com.quyl.poi.enums.KeyTypeEnum;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class PoiExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(PoiExcelUtil.class);
    private static final String XLS = ".xls";
    private static final String XLSX = ".xlsx";

    /**
     * 根据文件后缀获取对应Workbook对象
     *
     * @param filePath
     * @param fileType
     * @return
     */
    public static Workbook getWorkbook(String filePath, String fileType) {
        Workbook workbook = null;
        FileInputStream fileInputStream = null;
        try {
            File excelFile = new File(filePath);
            if (!excelFile.exists()) {
                logger.info(filePath + "文件不存在");
                return null;
            }
            fileInputStream = new FileInputStream(excelFile);
            if (fileType.equalsIgnoreCase(XLS)) {
                workbook = new HSSFWorkbook(fileInputStream);
            } else if (fileType.equalsIgnoreCase(XLSX)) {
                workbook = new XSSFWorkbook(fileInputStream);
            }
        } catch (Exception e) {
            logger.error("获取文件失败", e);
        } finally {
            try {
                if (null != fileInputStream) {
                    fileInputStream.close();
                }
            } catch (Exception e) {
                logger.error("关闭数据流出错！错误信息：", e);
                return null;
            }
        }
        return workbook;
    }

    /**
     * 将每一行数据转化为一个Map对象
     *
     * @param row       行对象
     * @param cellCount 列数
     * @param mapKey    表头Map
     * @return
     */
    public static Map<String, Object> convertRowToData(Row row, int cellCount, List<Map<String, String>> mapKey, List<Integer> useCellNums, String keyTypeEnum) {
        if (mapKey == null) {
            logger.info("没有表头信息");
            return null;
        }
        Map<String, Object> resultMap = new HashMap<>();
        int min = useCellNums.stream().reduce(Integer::min).get();
        int max = useCellNums.stream().reduce(Integer::max).get();
        cellCount = (max<=cellCount) ? max+1 : cellCount;
        Cell cell = null;
        for (int i = min; i < cellCount; i++) {
            cell = row.getCell(i);
            if (!useCellNums.contains(i)) {
                continue;
            }
            if (cell == null) {
                resultMap.put(mapKey.get(i).get(Constant.TITLE), "");
            } else {
                if (StrUtil.equalsIgnoreCase(keyTypeEnum, KeyTypeEnum.ROW_KEY.getCode())) {
                    resultMap.put(mapKey.get(i).get(Constant.TITLE), getCellVal(cell));
                }
                if (StrUtil.equalsIgnoreCase(keyTypeEnum, KeyTypeEnum.COL_KEY.getCode())) {
                    resultMap.put(mapKey.get(i).get(Constant.ID), getCellVal(cell));
                }
            }
        }
        return resultMap;
    }

    /**
     * 获取单元格的值
     *
     * @param cel
     * @return
     */
    public static String getCellVal(Cell cel) {
        if (cel.getCellType() == CellType.STRING) {
            return cel.getRichStringCellValue().getString();
        }
        if (cel.getCellType() == CellType.NUMERIC) {
            return cel.getNumericCellValue() + "";
        }
        if (cel.getCellType() == CellType.BOOLEAN) {
            return cel.getBooleanCellValue() + "";
        }
        if (cel.getCellType() == CellType.FORMULA) {
            return ((XSSFCell) cel).getRawValue();
//            return cel.getStringCellValue();
//            return cel.getCellFormula() + "";
        }
        return cel.toString();
    }

    public static List<Map<String, String>> getMapKeys(Sheet sheet, SheetCondition sheetCondition) {
        int firstRowCount = sheetCondition.getStartRow();
        Row firstRow = sheet.getRow(firstRowCount);//获取表头行
        int cellCount = firstRow.getLastCellNum();//取列
        List<Map<String, String>> mapKeys = new ArrayList<>();
        //获取表头信息，放在List中备用
        if (firstRow == null) {
            logger.info("解析Excel失败，未读到sheet表头信息！");
        } else {
            for (int i1 = 0; i1 < cellCount; i1++) {
                Map<String, String> mapLabel = new HashMap<>();
                mapLabel.put(Constant.ID, Constant.LABEL + i1);
                //sheet1定制化
                if (KeyTypeEnum.ROW_KEY.getCode().equals(sheetCondition.getKeyType())) {
                    if (sheetCondition.getCellInfo().containsKey(i1)) {
                        mapLabel.put(Constant.TITLE, sheetCondition.getCellInfo().get(i1));
                    }
                } else if (ObjectUtils.isEmpty(firstRow.getCell(i1))) {
                    mapLabel.put(Constant.TITLE, Constant.NULLSTR);
                } else {
                    mapLabel.put(Constant.TITLE, firstRow.getCell(i1).toString());
                }
                mapKeys.add(mapLabel);
            }
        }
        return mapKeys;
    }

    /**
     * 解析每行数据
     *
     * @param mapKeys
     * @param sheet
     * @param sheetCondition
     * @return
     */
    public static List<Map<String, Object>> doSheetRowList(List<Map<String, String>> mapKeys, Sheet sheet, SheetCondition sheetCondition) {
        List<Integer> useCellNums = sheetCondition.getUseCellNums();

        int firstRowCount = sheetCondition.getStartRow();
        int cellCount = sheet.getRow(firstRowCount).getLastCellNum();//取列
        //
        int rowStart = firstRowCount + 1;
        int rowEnd = sheet.getPhysicalNumberOfRows();
        List<Map<String, Object>> dataList = new ArrayList<>(rowEnd-rowStart+1);
        for (int j = rowStart; j < rowEnd; j++) {
            Row row = sheet.getRow(j);//获取对应的row对象
            if (row == null || StringUtils.isEmpty(PoiExcelUtil.getCellVal(row.getCell(sheetCondition.getIndexNum())))) {
                break;
            }
            //将每一行数据转化为一个Map对象
            Map<String, Object> rowMap = PoiExcelUtil.convertRowToData(row, cellCount, mapKeys, useCellNums, sheetCondition.getKeyType());
            dataList.add(rowMap);
        }
        return dataList;
    }
}
